from peewee import *
from playhouse.pool import PooledMySQLDatabase

mysql_db = PooledMySQLDatabase(
    database='ssq',
    timeout=3600 * 2,
    user='root',
    password='root',
    host='127.0.0.1',
    port=3306
)


class Ssq(Model):
    """
    双色球模型类
    """
    public_date = DateField()  # 开奖日期
    public_num = CharField()  # 开奖期号
    red_ball = CharField()  # 红球号码
    blue_ball = CharField()  # 篮球号码

    def __str__(self):
        return "Ssq(public_date='%s', public_num='%s', red_ball='%s, blue_ball='%s')" % (
            self.public_date, self.public_num, self.red_ball, self.blue_ball)

    class Meta:
        database = mysql_db


mysql_db.connect()  # 连接数据库


class SsqDb:
    def all(self):
        """
        返回数据库中所有的双色球数据
        """
        return Ssq.select().order_by(Ssq.public_date.desc())

    def is_exists(self, public_date):
        """
        更加开奖日期判断数据是否存在于数据库
        :param public_date: 开奖日期
        """
        result = Ssq.select().where(Ssq.public_date == public_date)
        for i in result:
            if i is not None:
                return True
        return False

    def page(self, page, size=20):
        """
        分页查询
        :param page:第几页
        :param size: 每页数量
        :return: 分页数据
        """
        page_data = Ssq.select().order_by(Ssq.public_date.desc()).paginate(page, size)
        page_data = [{
            'id': i.id,
            'public_date': i.public_date,
            'public_num': i.public_num,
            'red_ball': i.red_ball,
            'blue_ball': i.blue_ball
        } for i in page_data]
        return {
            'code': 0,
            'msg': 'ok',
            'count': self.count(),
            'data': page_data
        }

    def count(self):
        """
        查询数据库中的记录总数
        """
        count = Ssq.select().count()
        return count

    def add_by_tuple(self, ssq_tuple):
        ssq = Ssq()
        ssq.public_date = ssq_tuple[0]
        ssq.public_num = ssq_tuple[1]
        ssq.red_ball = ssq_tuple[2]
        ssq.blue_ball = ssq_tuple[3]
        if not self.is_exists(ssq.public_date):
            ssq.save()
            print("添加成功")
        else:
            print("数据已存在")

    def get_latest_red_tuple(self):
        """
        获取最新的红球数据,并转换为元组
        """
        red_ball = Ssq.select(Ssq.red_ball).order_by(Ssq.public_date.desc()).first()
        red_ball_tuple = tuple(map(lambda x: int(x), red_ball.red_ball.split(',')))
        return red_ball_tuple

    def get_latest_5_blue_tuple(self):
        """
        获取最新5期的篮球数据
        """
        query = Ssq.select(Ssq.blue_ball).order_by(Ssq.public_date.desc()).limit(5)
        blue_ball_tuple = tuple(map(lambda x: int(x.blue_ball), query))
        return blue_ball_tuple

    def get_red_tuple_by_public_num_tuple(self, public_num):
        """
        根据双色球期号获取红球数据
        :param public_num: 双色球期号
        """
        result = Ssq.select(Ssq.red_ball).filter(public_num=public_num).first()
        red_ball_tuple = tuple(map(lambda x: int(x), result.red_ball.split(',')))
        return red_ball_tuple

    def get_before_5_blue_tuple_by_public_num(self, public_num):
        """
        根据指定的期号获取篮球数据
        :param public_num: 指定期号
        """
        blue_ball = Ssq.select(Ssq.blue_ball).filter(Ssq.public_num < public_num).order_by(
            Ssq.public_date.desc()).limit(5)
        blue_ball_tuple = tuple(map(lambda x: x.blue_ball, blue_ball))
        return blue_ball_tuple

    def get_public_num_all(self):
        """
        获取所有的双色球开奖期号
        """
        # public_num_list = session.query(Ssq.id, Ssq.public_num).order_by(Ssq.public_num.desc()).all()
        public_num_list = Ssq.select(Ssq.id, Ssq.public_num).order_by(Ssq.public_num.desc())
        return list(map(lambda x: (x.id, x.public_num), public_num_list))

    def get_ball_data_by_id(self, id):
        """
        根据ID获取篮球和红球数据
        :param id: ID
        """
        # ball = session.query(Ssq.public_num, Ssq.red_ball, Ssq.blue_ball).filter_by(id=id).first()
        # ball = Ssq.select(Ssq.public_num, Ssq.red_ball, Ssq.blue_ball).filter(id=id)
        ball = Ssq.select(Ssq.public_num, Ssq.red_ball, Ssq.blue_ball).where(Ssq.id == id).first()
        return (ball.public_num, ball.red_ball, ball.blue_ball)


# Base.metadata.create_all(engine)
if __name__ == '__main__':
    ssqDb = SsqDb()
    # 查询最新的红球
    print(ssqDb.get_public_num_all())
